import logging
import constant

import pandas as pd
from sqlalchemy import create_engine

from ListUtils import anti_join

"""
根据THS行业数据 添加
此数据需要从THS软件中导出源数据文件
"""

logging.basicConfig(level=logging.DEBUG, format=' %(asctime)s - %(levelname)s - %(message)s')
logging.debug('Start of program')

# 初始化数据库连接:
engine = create_engine(
    constant.get_db_path(),
    max_overflow=0,   # 超过连接池大小外最多创建的连接数
    pool_size=5,      # 连接池大小
    pool_timeout=30,  # 连接池中没有线程最多等待时间，否则报错
    pool_recycle=-1,  # 多久之后对连接池中的连接进行回收（重置）-1不回收
)


## 个股信息 包含L1和L2
ndf = pd.read_csv("../../source/Table.txt", encoding='gbk',delim_whitespace =True)
# print(ndf)
# print(type(ndf))
ndf.replace('\t','', inplace=True)
# print(ndf.loc[0])
ndf['代码'] = ndf['代码'].str[2:] + '.' + ndf['代码'].str[:2]
# print(ndf['代码'])
ndf.columns = [ 'ts_code','name', 'l2_name', 'l1_name']
# df_industry.to_sql('index_classify', con=engine, index=False, index_label='', if_exists='append')
# df_industry2 = ndf[['细分行业']]

# L1 信息
source_list1 = pd.read_csv("../../source/ths_industry.txt", encoding='gbk',delim_whitespace =True)
source_list1.columns = [ 'l1_code','l1_name']
# 从行业总信息中分离出L2信息样本
source_list2 = pd.read_csv("../../source/ths_industry2.txt", encoding='gbk',delim_whitespace =True)
source_list2.columns = [ 'l1_code','l1_name']
source_list2 = anti_join(source_list2, source_list1, on=[ 'l1_code','l1_name'])
source_list2.columns = [ 'l2_code','l2_name']
print(source_list2)


# source_list1['level'] = 'L1'
# source_list1['src'] = 'THS'
# source_list1.to_sql('index_classify', con=engine, index=False, index_label='ts_code', if_exists='append')
# print(source_list1)



new_list = pd.merge(ndf, source_list1, on='l1_name')
print(new_list)

new_list = pd.merge(new_list, source_list2, on='l2_name')
print(new_list.loc[0])

new_list = new_list[['l2_code', 'l2_name', 'l1_code']]
new_list.drop_duplicates(subset=['l2_code', 'l2_name'],keep='first',inplace=True)
print(new_list.loc[0])
new_list.columns = ['ts_code', 'industry_name', 'parent_code']
new_list['level'] = 'L2'
new_list['src'] = 'THS'
new_list.to_sql('index_classify', con=engine, index=False, index_label='ts_code', if_exists='append')
# ndf.drop(['Unnamed: 1', 'Unnamed: 3', 'Unnamed: 5', 'Unnamed: 7', 'Unnamed: 8'], axis=1,  inplace=True)
# print(ndf.loc[0])
# ndf = ndf[['代码', '名称', '细分行业', '所属行业']]
# print(ndf)
# ndf = ndf[0]
# df_industry = ndf[['指数代码', '指数名称']]
# df_industry.drop_duplicates(subset=['指数代码', '指数名称'],keep='first',inplace=True)
# df_industry.reset_index(inplace=True)
# df_industry.to_sql('index_classify', con=engine, index=False, index_label='', if_exists='append')
# print(df_industry)


logging.debug(' end of program ')